import mysql.connector
from mysql.connector import Error
import datetime

def create_connection():
    print('cre conn')
    try:
        connection = mysql.connector.connect(
            host='43.133.152.186',       # MySQL服务器地址
            port=3306,
            database='school-cafe',     # 数据库名称
            user='root',   # 用户名
            password='zsq19951212&^.',  # 密码
        )
        if connection.is_connected():
            print("成功连接到数据库")
            return connection
    except Error as e:
        print(f"连接失败: {e}")
        return None
    

def update_nick_name(connection, email, nickName):
    update_sql = f'''update  users set `nick_name` = '{nickName}'  where email = '{email}' '''
    print(update_sql)
    cursor = connection.cursor()
    try:
        cursor.execute(update_sql)
        connection.commit()
        print("更新名称成功")
        connection.close()
        return True
    except Error as e:
        print(f"数据库链接错误. {e}")
        connection.close()
        return False
    

def update_password(connection, email, password):
    update_sql = f'''update  users set `password` = '{password}'  where email = '{email}' '''
    print(update_sql)
    cursor = connection.cursor()
    try:
        cursor.execute(update_sql)
        connection.commit()
        print("更新名称成功")
        connection.close()
        return True
    except Error as e:
        print(f"数据库链接错误. {e}")
        connection.close()
        return False


def update_avatar(connection, email, avatar):
    update_sql = f'''update  users set `head_img` = '{avatar}'  where email = '{email}' '''
    print(update_sql)
    cursor = connection.cursor()
    try:
        cursor.execute(update_sql)
        connection.commit()
        print("更新头像成功")
        connection.close()
        return True
    except Error as e:
        print(f"数据库链接错误. {e}")
        connection.close()
        return False


def query_user(connection, email):
    query_sql = f"SELECT * FROM users where email = '{email}' limit 1"
    print(query_sql)
    cursor = connection.cursor()
    try:
        cursor.execute(query_sql)
        records = cursor.fetchall()
        if len(records) > 0:
            return {
                'email': records[0][0],
                'headImg': records[0][3],
                'nickName': records[0][2],
                'password': records[0][1],
                'role': records[0][4],
            }
        connection.close()
        return None
    except Error as e:
        print(f"数据库链接错误 {e}")
        connection.close()
        return None
    

def add_user(connection, params):
    insert_sql = f'''INSERT INTO users (`email`, `password`, `nick_name`, `head_img`, `role`) 
                    VALUES ('{params['email']}', '{params['password']}', '{params['nick_name']}', '{params['head_img']}', 
                    1)
                '''
    print(insert_sql)
    cursor = connection.cursor()
    try:
        cursor.execute(insert_sql)
        connection.commit()
        print("新增用户成功")
        connection.close()
        return True
    except Error as e:
        print(f"数据库链接错误. {e}")
        connection.close()
        return False


def query_sub_category(connection, category):
    query_sql = f"SELECT * FROM food_category where `type` = '{category}'"
    print(query_sql)
    result = []
    cursor = connection.cursor()
    try:
        cursor.execute(query_sql)
        records = cursor.fetchall()
        for record in records:
            result.append({
                'id': record[0],
                'name': record[1],
                'img': record[2],
                'all': record[4]
            })
        connection.close()
    except Error as e:
        print(f"数据库链接错误 {e}")
        connection.close()
    return result

def query_food(connection, category, type):
    if category:
        query_sql = f"SELECT * FROM food where `category` = '{category}'"
    else:
        query_sql = f"SELECT * FROM food where `type` = '{type}'"
    print(query_sql)
    result = []
    cursor = connection.cursor()
    try:
        cursor.execute(query_sql)
        records = cursor.fetchall()
        for record in records:
            result.append({
                'id': record[0],
                'name': record[1],
                'img': record[2],
                'price': record[3],
                'ingredients': record[4],
                'category': record[5],
                'type': record[6]
            })
        connection.close()
    except Error as e:
        print(f"数据库链接错误 {e}")
        connection.close()
    return result


def query_cart(connection, customer_id):
    query_sql = f'''
        SELECT c.id, c.food_id, c.`number`, c.`category`, f.`name`, f.`price` 
        FROM cart c join food f on c.`food_id` = f.`id`
        where c.`customer_id` = '{customer_id}' 
    '''
    print(query_sql)
    result = []
    cursor = connection.cursor()
    try:
        cursor.execute(query_sql)
        records = cursor.fetchall()
        for record in records:
            result.append({
                'cartId': record[0],
                'foodId': record[1],
                'number': record[2],
                'category': record[3],
                'name': record[4],
                'price': record[5],
                'customerId': customer_id
            })
        connection.close()
    except Error as e:
        print(f"数据库链接错误 {e}")
        connection.close()
    return result


def create_order(connection, params):
    current_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
    insert_sql = f'''INSERT INTO `order` (`id`, `total_price`, `created_time`, `status`, `customer_id`, `category`) 
                    VALUES ('{params['order_id']}', '{params['total_price']}', '{current_time}', 0, '{params['customer_id']}',
                    '{params['category']}'
                    )
                '''
    print(insert_sql)
    cursor = connection.cursor()
    try:
        cursor.execute(insert_sql)
        connection.commit()
        print("生产订单成功")
        connection.close()
        return True
    except Error as e:
        print(f"数据库链接错误. {e}")
        connection.close()
        return False

def create_order_detail(connection, params):
    insert_sql = f'''INSERT INTO order_detail ( `food_id`, `number`, `order_id`) 
                    VALUES ('{params['food_id']}', '{params['number']}', '{params['order_id']}')
                '''
    print(insert_sql)
    cursor = connection.cursor()
    try:
        cursor.execute(insert_sql)
        connection.commit()
        print("订单明细添加成功")
        connection.close()
        return True
    except Error as e:
        print(f"数据库链接错误. {e}")
        connection.close()
        return False
    


# query order by pk
def query_order(connection, order_id):
    query_sql = f"SELECT * FROM `order` where `id` = '{order_id}'"
    print(query_sql)
    cursor = connection.cursor()
    try:
        cursor.execute(query_sql)
        records = cursor.fetchall()
        for record in records:
            return {
                'id': order_id,
                'total_price': record[1],
                'created_time': record[2],
                'status': record[3],
                'customer_id': record[4],
                'payment': record[5],
                'card_id': record[6],
                'pay_time': record[7],
                'category': record[8]
            }
        connection.close()
    except Error as e:
        print(f"数据库链接错误 {e}")
        connection.close()
    return None


# query orders by specific customer
def query_customer_order(connection, customer_id):
    query_sql = f"SELECT * FROM `order` where `customer_id` = '{customer_id}'"
    print(query_sql)
    cursor = connection.cursor()
    result = []
    try:
        cursor.execute(query_sql)
        records = cursor.fetchall()
        for record in records:
            result.append({
                'id': record[0],
                'total_price': record[1],
                'created_time': record[2],
                'status': record[3],
                'customer_id': record[4],
                'payment': record[5],
                'card_id': record[6],
                'pay_time': record[7],
                'category': record[8]
            })
        connection.close()
    except Error as e:
        print(f"数据库链接错误 {e}")
        connection.close()
    return result



# query orders list
def query_all_order(connection):
    query_sql = f"SELECT * FROM `order` order by `created_time`"
    print(query_sql)
    cursor = connection.cursor()
    result = []
    try:
        cursor.execute(query_sql)
        records = cursor.fetchall()
        for record in records:
            result.append({
                'id': record[0],
                'total_price': record[1],
                'created_time': record[2],
                'status': record[3],
                'customer_id': record[4],
                'payment': record[5],
                'card_id': record[6],
                'pay_time': record[7],
                'category': record[8]
            })
        connection.close()
    except Error as e:
        print(f"数据库链接错误 {e}")
        connection.close()
    return result


def query_order_detail(connection, order_id):
    query_sql = f'''
            select food.id, order_detail.`number`, food.`name`, food.price 
            FROM order_detail INNER JOIN food
            on order_detail.food_id = food.id
            WHERE order_detail.order_id = '{order_id}'
    '''
    print(query_sql)
    cursor = connection.cursor()
    result = []
    try:
        cursor.execute(query_sql)
        records = cursor.fetchall()
        for record in records:
            result.append({
                'food_id': record[0],
                'number': record[1],
                'food_name': record[2],
                'food_price': record[3]
            })
        connection.close()
    except Error as e:
        print(f"数据库链接错误 {e}")
        connection.close()
    return result


def query_cart_food(connection, customer_id, food_id):
    query_sql = f'''
        SELECT * from cart where `customer_id` = '{customer_id}' and `food_id` = '{food_id}' limit 1
    '''
    print(query_sql)
    cursor = connection.cursor()
    try:
        cursor.execute(query_sql)
        records = cursor.fetchall()
        return len(records)
        # connection.close()
    except Error as e:
        print(f"数据库链接错误 {e}")
        # connection.close()
        return -1
    

def delete_cart_food(connection, record_id):
    query_sql = f'''
        DELETE from cart where `id` = '{record_id}'
    '''
    print(query_sql)
    cursor = connection.cursor()
    try:
        cursor.execute(query_sql)
        connection.commit()
        return True
    except Error as e:
        print(f"数据库链接错误 {e}")
        # connection.close()
        return False


def add_to_cart(connection, cart_item):
    count = query_cart_food(connection, cart_item['customer_id'], cart_item['food_id'])
    if count > -1:
        if count == 0:
            insert_sql = f'''INSERT INTO cart ( `customer_id`, `food_id`, `number`, `category`) 
                    VALUES ('{cart_item['customer_id']}', '{cart_item['food_id']}', 1, '{cart_item['category']}')
                '''
            print(insert_sql)
            cursor = connection.cursor()
            try:
                cursor.execute(insert_sql)
                connection.commit()
                print("新增购物车成功")
                connection.close()
                return True
            except Error as e:
                print(f"数据库链接错误. {e}")
                connection.close()
                return False
        else:
           return update_cart_count(connection, cart_item['customer_id'], cart_item['food_id'], 1)
    else:
        return False
    

def add_to_cart2(connection, cart_item):
    count = query_cart_food(connection, cart_item['customer_id'], cart_item['food_id'])
    if count > -1:
        if count == 0:
            insert_sql = f'''INSERT INTO cart ( `customer_id`, `food_id`, `number`, `category`) 
                    VALUES ('{cart_item['customer_id']}', '{cart_item['food_id']}', {cart_item['number']}, '{cart_item['category']}')
                '''
            print(insert_sql)
            cursor = connection.cursor()
            try:
                cursor.execute(insert_sql)
                connection.commit()
                print("新增购物车成功")
                connection.close()
                return True
            except Error as e:
                print(f"数据库链接错误. {e}")
                connection.close()
                return False
        else:
           return update_cart_count(connection, cart_item['customer_id'], cart_item['food_id'], cart_item['number'])
    else:
        return False
    

def update_cart_count(connection, customer_id, food_id, value):
    update_sql = f'''update cart set `number` = `number` + {value}
                    where `customer_id` = '{customer_id}' and  `food_id` = '{food_id}'
                '''
    print(update_sql)
    cursor = connection.cursor()
    try:
        cursor.execute(update_sql)
        connection.commit()
        print("更新购物车数量")
        connection.close()
        return True
    except Error as e:
        print(f"数据库链接错误. {e}")
        connection.close()
        return False
    

def update_order_status(connection, params):
    # 用户取消订单
    if params['status'] != 1:
        update_sql = f'''update `order` set `status` = {params['status']}
                    where `id` = '{params['order_id']}'
                '''
    else:
        current_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        if params['payment'] == 0:
            update_sql = f'''update `order` set `status` = {params['status']},
                        `pay_time` = '{current_time}',
                        `payment` = {params['payment']},
                        `card_id` = '{params['card_id']}'
                        where `id` = '{params['order_id']}' 
                '''
        else:
            update_sql = f'''update `order` set `status` = {params['status']},
                        `pay_time` = '{current_time}',
                        `payment` = {params['payment']}
                        where `id` = '{params['order_id']}'
                '''
    cursor = connection.cursor()
    print(update_sql)
    try:
        cursor.execute(update_sql)
        connection.commit()
        print("更新订单状态成功")
        connection.close()
        return True
    except Error as e:
        print(f"数据库链接错误. {e}")
        connection.close()
        return False



def add_credit_card(connection, params):
    insert_sql = f'''INSERT INTO card ( `id`, `expire_date`, `cvv`, `name`, `is_default`, `customer_id`) 
        VALUES ('{params['id']}', '{params['expire_date']}', '{params['cvv']}', '{params['name']}', 0, '{params['customer_id']}')
    '''
    print(insert_sql)
    cursor = connection.cursor()
    try:
        cursor.execute(insert_sql)
        connection.commit()
        print("添加信用卡成功")
        connection.close()
        return True
    except Error as e:
        print(f"数据库链接错误. {e}")
        connection.close()
        return False


def query_credit_card(connection, customer_id):
    query_sql = f'''
            select * from card where `customer_id` = '{customer_id}'
    '''
    print(query_sql)
    result = []
    cursor = connection.cursor()
    try:
        cursor.execute(query_sql)
        records = cursor.fetchall()
        for record in records:
            result.append({
                'id': record[0],
                'expire_date': record[1],
                'cvv': record[2],
                'name': record[3],
                'is_default': record[4],
                'customer_id': customer_id
            })
        connection.close()
    except Error as e:
        print(f"数据库链接错误 {e}")
        connection.close()
    return result


def update_credit_card(connection, card_no, email):
    update_sql = f'''
        update card set `is_default` = 1 where `id` = '{card_no}'
    '''

    update_sql1 = f'''
        update card set `is_default` = 0 where `id` != '{card_no}' and `customer_id` = '{email}'
    '''
    print(update_sql)
    print(update_sql1)
    cursor = connection.cursor()
    try:
        cursor.execute(update_sql)
        cursor.execute(update_sql1)
        connection.commit()
        connection.close()
        return True
    except Error as e:
        print(f"数据库链接错误 {e}")
        connection.close()
        return False


def delete_credit_card(connection, card_no):
    delete_sql = f'''
            delete from card where `id` = '{card_no}'
    '''
    print(delete_sql)
    cursor = connection.cursor()
    try:
        cursor.execute(delete_sql)
        connection.commit()
        connection.close()
        return True
    except Error as e:
        print(f"数据库链接错误 {e}")
        connection.close()
        return False

def query_open_time(connection):
    query_sql = f'''
            select * from open_time
    '''
    print(query_sql)
    result = []
    cursor = connection.cursor()
    try:
        cursor.execute(query_sql)
        records = cursor.fetchall()
        for record in records:
            result.append({
                'id': record[0],
                'open': record[1],
                'close': record[2]
            })
        connection.close()
    except Error as e:
        print(f"数据库链接错误 {e}")
        connection.close()
    return result

# query_cart(create_connection(), '12343434324@gmail.com')